<?php

namespace app\controllers;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Db;

class Btc
{
    private $table_yx_6100p = 'yx_6100p';
    private $table_yx_7100p = 'yx_7100p';

    public function index()
    {
        // 分组数据
        $data = Db::table($this->table_yx_6100p)
            ->group('model')
            ->order(['make' => 'asc', 'model' => 'asc'])
            ->select();
        // 生成表格
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        //设置单元格内容
        $sheet->setCellValueByColumnAndRow(1, 1, 'Year');
        $sheet->setCellValueByColumnAndRow(2, 1, 'Make');
        $sheet->setCellValueByColumnAndRow(3, 1, 'Model');
        $sheet->setCellValueByColumnAndRow(4, 1, 'Check Engine');
        $sheet->setCellValueByColumnAndRow(5, 1, 'ABS');
        $sheet->setCellValueByColumnAndRow(6, 1, 'SRS');
//        $sheet->setCellValueByColumnAndRow(7, 1, 'BATTERY PROCEDURE');
        $sheet->setCellValueByColumnAndRow(7, 1, 'OIL RESET PROCEDURE');
        $i = 0;
        foreach ($data as $key => $value) {
            $ii = 1996;
            for ($x = 0; $x < 25; $x++) {
                $sheet->setCellValueByColumnAndRow(1, $i + 2, $x + $ii);
                $sheet->setCellValueByColumnAndRow(2, $i + 2, $value['make']);
                $sheet->setCellValueByColumnAndRow(3, $i + 2, $value['model']);
                $sheet->setCellValueByColumnAndRow(4, $i + 2, $value['check_engine']);
                $sheet->setCellValueByColumnAndRow(5, $i + 2, $value['abs']);
                $sheet->setCellValueByColumnAndRow(6, $i + 2, $value['srs']);
                $sheet->setCellValueByColumnAndRow(7, $i + 2, $value['battery_proceoure']);
//                $sheet->setCellValueByColumnAndRow(8, $i + 2, $value['oil_reset_proceoure']);
                $i = $i + 1;
            }
        }
        $writer = new Xlsx($spreadsheet);
        $writer->save(__DIR__ . '/../../public/yx/6100P_FB.xlsx');
//        $writer->save(__DIR__ . '/../../public/yx/7100P_FB.xlsx');
        dump('ok');
    }

    public function bf()
    {
//        $json_string_cx_fb = file_get_contents(__DIR__ . '/../../public/yx/6100P.json');
        $json_string_cx_fb = file_get_contents(__DIR__ . '/../../public/yx/7100P.json');
        $data = json_decode($json_string_cx_fb, true);
        // 生成表格
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        //设置单元格内容
        $sheet->setCellValueByColumnAndRow(1, 1, 'Year');
        $sheet->setCellValueByColumnAndRow(2, 1, 'Make');
        $sheet->setCellValueByColumnAndRow(3, 1, 'Model');
        $sheet->setCellValueByColumnAndRow(4, 1, 'Check Engine');
        $sheet->setCellValueByColumnAndRow(5, 1, 'ABS');
        $sheet->setCellValueByColumnAndRow(6, 1, 'SRS');
        $sheet->setCellValueByColumnAndRow(7, 1, 'BATTERY PROCEDURE');
        $sheet->setCellValueByColumnAndRow(8, 1, 'OIL RESET PROCEDURE');
        $i = 0;
        foreach ($data as $key => $value) {
            foreach ($value as $k => $v) {
                $sheet->setCellValueByColumnAndRow(1, $i + 2, $v[0]);
                $sheet->setCellValueByColumnAndRow(2, $i + 2, $v[1]);
                $sheet->setCellValueByColumnAndRow(3, $i + 2, $v[2]);
                $sheet->setCellValueByColumnAndRow(4, $i + 2, $v[3]);
                $sheet->setCellValueByColumnAndRow(5, $i + 2, $v[4]);
                $sheet->setCellValueByColumnAndRow(6, $i + 2, $v[5]);
                $sheet->setCellValueByColumnAndRow(7, $i + 2, $v[6]);
                $sheet->setCellValueByColumnAndRow(8, $i + 2, $v[7]);
                $i = $i + 1;
            }
        }
        $writer = new Xlsx($spreadsheet);
//        $writer->save(__DIR__ . '/../../public/yx/6100P.xlsx');
        $writer->save(__DIR__ . '/../../public/yx/7100P.xlsx');
        dump('ok');
    }

    public function dr()
    {
//        $json_string_cx_fb = file_get_contents(__DIR__ . '/../../public/yx/6100P.json');
        $json_string_cx_fb = file_get_contents(__DIR__ . '/../../public/yx/7100P.json');
        $data = json_decode($json_string_cx_fb, true);

        foreach ($data as $key => $value) {
            foreach ($value as $k => $v) {
                DB::table($this->table_yx_7100p)
                    ->insert([
                        'year' => $v[0],
                        'make' => $v[1],
                        'model' => $v[2],
                        'check_engine' => $v[3],
                        'abs' => $v[4],
                        'srs' => $v[5],
                        'battery_proceoure' => $v[6],
                        'oil_reset_proceoure' => $v[7],
                    ]);
            }
        }
        dump('ok');
    }
}
